"""数据库连接池管理模块 - 提供异步SQLite连接池功能。"""

import os
import sqlite3
import aiosqlite
import threading
import time
from tenacity import retry, stop_after_attempt, wait_exponential, retry_if_exception_type
from .logger import logger

# 全局连接池管理
_connection_pool = None
_pool_lock = threading.Lock()
_pool_initialized = False

class ConnectionPoolError(Exception):
    """自定义异常，表示数据库连接池初始化或状态异常"""
    pass

# 数据库重试机制,最多重试3次,指数退避等待2-10秒,仅对数据库操作错误重试
@retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=2, max=10), retry=retry_if_exception_type((sqlite3.OperationalError, aiosqlite.OperationalError)))
async def test_async_connection(conn):
    """测试异步连接是否可用"""
    try:
        async with conn.execute("SELECT 1") as cursor:
            result = await cursor.fetchone()
            if result != (1,):
                raise ConnectionPoolError("连接池测试查询失败，返回结果异常")
        return True
    except Exception as e:
        logger.error(f"Async connection test failed: {e}")
        raise ConnectionPoolError(f"异步连接测试失败: {e}")

@retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=2, max=10))
async def get_async_connection():
    """获取异步数据库连接，使用连接池管理"""
    global _connection_pool, _pool_initialized
    
    with _pool_lock:
        if _connection_pool is None or not _pool_initialized:
            try:
                # 确保数据库文件目录存在
                db_path = "checkpoints.db"
                db_dir = os.path.dirname(os.path.abspath(db_path))
                os.makedirs(db_dir, exist_ok=True)
                
                # 创建连接池
                _connection_pool = await aiosqlite.connect(db_path)
                
                # 测试连接
                await test_async_connection(_connection_pool)
                
                _pool_initialized = True
                logger.info("AsyncSQLite connection pool initialized and tested successfully")
            except Exception as e:
                logger.error(f"Failed to initialize connection pool: {e}")
                _connection_pool = None
                _pool_initialized = False
                raise ConnectionPoolError(f"Connection pool initialization failed: {e}")
    
    # 验证连接状态
    if _connection_pool is None:
        raise ConnectionPoolError("Connection pool is None")
        
    return _connection_pool

async def reset_connection_pool():
    """重置连接池状态"""
    global _connection_pool, _pool_initialized
    
    with _pool_lock:
        if _connection_pool:
            try:
                await _connection_pool.close()
                logger.info("Previous connection pool closed")
            except Exception as e:
                logger.warning(f"Error closing previous connection pool: {e}")
        
        _connection_pool = None
        _pool_initialized = False
        logger.info("Connection pool reset completed")

def monitor_connection_pool(interval: int = 60):
    """周期性监控连接池状态"""
    def _monitor():
        global _connection_pool, _pool_initialized
        while True:
            try:
                with _pool_lock:
                    if _connection_pool is None:
                        logger.info("Connection pool status: Not initialized")
                    elif not _pool_initialized:
                        logger.warning("Connection pool status: Initialization failed")
                    else:
                        # 检查连接是否仍然有效
                        try:
                            # 这里可以添加更详细的连接健康检查
                            logger.info("Connection pool status: Healthy")
                        except Exception as e:
                            logger.warning(f"Connection pool health check failed: {e}")
                            _pool_initialized = False
            except Exception as e:
                logger.error(f"Failed to monitor connection pool: {e}")
            time.sleep(interval)

    monitor_thread = threading.Thread(target=_monitor, daemon=True)
    monitor_thread.start()
    return monitor_thread

def get_connection_pool_status():
    """获取连接池状态信息"""
    global _connection_pool, _pool_initialized
    
    with _pool_lock:
        return {
            "pool_exists": _connection_pool is not None,
            "pool_initialized": _pool_initialized,
            "pool_object": _connection_pool
        }